package com.infore.statisticsAndAlarm.service.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.infore.statisticsAndAlarm.externalInterface.IDataReceivingService;
import com.infore.statisticsAndAlarm.repository.IDayDataRepository;
import com.infore.statisticsAndAlarm.service.IStatisticsDataService;
import com.infore.statisticsAndAlarm.util.DateUtil;
import com.infore.statisticsAndAlarm.util.ObjectMapperUtil;
import com.infore.statisticsAndAlarm.util.ResultEntity;
import com.infore.statisticsAndAlarm.util.ScaleUtil;
import com.infore.statisticsAndAlarm.util.UUIDGenerator;
import com.infore.statisticsAndAlarm.util.WaterQualityUtil;

@Service
public class StatisticsDataServiceImpl implements IStatisticsDataService {
    
    @Autowired
    IDataReceivingService IDataReceivingService;
    
    @Autowired
    ObjectMapperUtil ObjectMapperUtil;
    
    @Autowired
    IDayDataRepository IDayDataRepository;
    
    @Autowired
    DataSource DataSource;
    
    Connection connection = null;
    PreparedStatement pstmt = null;
    
    String waterQuality = ""; // 默认水质等级为空

    @SuppressWarnings("unchecked")
    @Override
    public void getDayDataList() {
        System.out.println("****************" + DateUtil.getCurrentDate(DateUtil.DATE_FULL_STR) + " 开始统计日数据****************");
        // 获取日数据集合
        ResultEntity resultEntity = IDataReceivingService.getDayData();
        // 获取未统计周、月、季度、年数据时间集合
        ResultEntity weekResultEntity = IDataReceivingService.getUnStatisticsWeekDataTime();
        ResultEntity monthResultEntity = IDataReceivingService.getUnStatisticsMonthDataTime();
        ResultEntity quarterResultEntity = IDataReceivingService.getUnStatisticsQuarterDataTime();
        ResultEntity yearResultEntity = IDataReceivingService.getUnStatisticsYearDataTime();
        List<Map<String, Object>> dayDataList = ObjectMapperUtil.getMapList(resultEntity);
        if (dayDataList.size() == 0) {
            System.out.println("暂无需要统计的数据！");
        } else {
            String weekFlag = "0", monthFlag = "0", quarterFlag = "0", yearFlag = "0"; // 默认标识为0
            String standardNum = "6";
            try {
                connection = DataSource.getConnection();
                // 编写Sql
                String sql = "MERGE INTO DAY_DATA T1 " 
                            + "USING "
                            + "(SELECT COUNT(*) NUM FROM DAY_DATA T WHERE T.STATION_CODE = ? AND T.FACTOR_CODE = ? " 
                            + "AND T.DATA_TIME = TO_DATE(?,'yyyy-MM-dd')) T2 "
                        + "ON (T2.NUM > 0) " 
                            + "WHEN MATCHED THEN "
                        + "UPDATE SET DATA_VALUE_AVG = ?,DATA_VALUE_MAX = ?,DATA_VALUE_MIN = ?,STANDARD_NUM = ?,REAL_NUM = ?,"
                        + "DATA_FLAG = ?,WATER_QUALITY = ?,MONTH_FLAG = ?,QUARTER_FLAG = ?,YEAR_FLAG = ?,WEEK_FLAG = ? "
                        + "WHERE STATION_CODE = ? AND FACTOR_CODE = ? AND DATA_TIME = TO_DATE(?,'yyyy-MM-dd') " 
                            + "WHEN NOT MATCHED THEN "
                        + "INSERT VALUES (?,?,?,TO_DATE(?,'yyyy-MM-dd'),?,?,?,?,?,?,?,?,?,?,?) ";
                pstmt = connection.prepareStatement(sql);
                int k = 0;
                for (Map<String, Object> map : dayDataList) {
                    String stationCode = map.get("MN").toString();
                    String projectCode = map.get("FACTOR_CODE").toString();
                    String dateTime = map.get("TIME").toString();
                    String dataValueAvg = map.get("DATA_VALUE_AVG") == null ? "0" : map.get("DATA_VALUE_AVG").toString();
                    String dataValueMax = map.get("DATA_VALUE_MAX") == null ? "0" : map.get("DATA_VALUE_MAX").toString();
                    String dataValueMin = map.get("DATA_VALUE_MIN") == null ? "0" : map.get("DATA_VALUE_MIN").toString();
                    String realNum = map.get("REAL_NUM") == null ? "0" : map.get("REAL_NUM").toString();
                    // 判断数据的有效性
                    String dataFlag = "N"; //默认为有效
                    if (Integer.valueOf(realNum) >= 4) { //一天内必须至少要有4个小时的数据，数据才具备有效性，否则有效性不足（一天6条小时数据，4小时一条）
                        dataFlag = "N";
                    } else {
                        dataFlag = "H";
                    }
                    // 计算因子的水质等级
                    waterQuality = WaterQualityUtil.getWaterLevel(projectCode, dataValueAvg) + "";
                    // 修约数据
                    dataValueAvg = ScaleUtil.sciCalSix(Double.valueOf(dataValueAvg), 4);
                    String uuid = UUIDGenerator.getUUID();
                    // 判断是否存在
                    pstmt.setString(1, stationCode);
                    pstmt.setString(2, projectCode);
                    pstmt.setString(3, dateTime);
                    pstmt.setString(4, dataValueAvg);
                    pstmt.setString(5, dataValueMax);
                    pstmt.setString(6, dataValueMin);
                    pstmt.setString(7, standardNum);
                    pstmt.setString(8, realNum);
                    pstmt.setString(9, dataFlag);
                    pstmt.setString(10, waterQuality);
                    pstmt.setString(11, monthFlag);
                    pstmt.setString(12, quarterFlag);
                    pstmt.setString(13, yearFlag);
                    pstmt.setString(14, weekFlag);
                    pstmt.setString(15, stationCode);
                    pstmt.setString(16, projectCode);
                    pstmt.setString(17, dateTime);
                    pstmt.setString(18, uuid);
                    pstmt.setString(19, stationCode);
                    pstmt.setString(20, projectCode);
                    pstmt.setString(21, dateTime);
                    pstmt.setString(22, dataValueAvg);
                    pstmt.setString(23, dataValueMax);
                    pstmt.setString(24, dataValueMin);
                    pstmt.setString(25, realNum);
                    pstmt.setString(26, standardNum);
                    pstmt.setString(27, dataFlag);
                    pstmt.setString(28, waterQuality);
                    pstmt.setString(29, monthFlag);
                    pstmt.setString(30, quarterFlag);
                    pstmt.setString(31, yearFlag);
                    pstmt.setString(32, weekFlag);
                    pstmt.addBatch();
                    k++;
                    if (k % 20 == 0) {
                        pstmt.executeBatch();
                    }
                }
                pstmt.executeBatch();
                ResultEntity hourResultEntity = IDataReceivingService.updateHourFlag();
                Map<String, Object> map = (Map<String, Object>) hourResultEntity.getContent();
                System.out.println("日数据" + k + "条统计成功; 小时数据" + map.get("updateHourNum").toString() + "条标识更新成功！");
                String weekMessage = getWeekDataList(connection, pstmt, weekResultEntity);
                System.out.println(weekMessage);
                String monthMessage = getMonthDataList(connection, pstmt, monthResultEntity);
                System.out.println(monthMessage);
                String quarterMessage = getQuarterDataList(connection, pstmt, quarterResultEntity);
                System.out.println(quarterMessage);
                String yearMessage = getYearDataList(connection, pstmt, yearResultEntity);
                System.out.println(yearMessage);
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (connection != null) {
                        connection.close();
                    }
                    if (pstmt != null) {
                        pstmt.close();
                    }
                } catch (SQLException e) {
                        e.printStackTrace();
                }
            }
        }
    }
    
    @Override
    public String getWeekDataList(Connection connection, PreparedStatement pstmt, ResultEntity resultEntity) {
        System.out.println("****************" + DateUtil.getCurrentDate(DateUtil.DATE_FULL_STR) + " 开始统计周数据****************");
        String message = "";
        List<Map<String, Object>> weekList = ObjectMapperUtil.getMapList(resultEntity);
        // 获取年、季度时间
        List<String> yearTimeList = new ArrayList<String>();
        List<String> weekTimeList = new ArrayList<String>();
        for (Map<String, Object> map : weekList) {
            String year = map.get("YEAR").toString();
            String week = map.get("WEEK").toString();
            yearTimeList.add(year);
            weekTimeList.add(week);
        }
        if (yearTimeList.size() == 0 || weekTimeList.size() == 0) {
            message = "周数据暂无需要统计的数据！";
            return message;
        }
        List<Map<String, Object>> weekListData = IDayDataRepository.getCountWeekData(yearTimeList, weekTimeList);
        try {
            // 编写Sql
            String sql = "MERGE INTO WEEK_DATA T1 " 
                        + "USING "
                        + "(SELECT COUNT(*) NUM FROM WEEK_DATA T WHERE T.STATION_CODE = ? AND T.FACTOR_CODE = ? " 
                        + "AND T.DATA_WEEK = ? AND T.DATA_YEAR = ? ) T2 "
                    + "ON (T2.NUM > 0) " 
                        + "WHEN MATCHED THEN "
                    + "UPDATE SET DATA_VALUE_AVG = ?,DATA_VALUE_MAX = ?,DATA_VALUE_MIN = ?,STANDARD_NUM = ?,REAL_NUM = ?,"
                    + "DATA_FLAG = ?,WATER_QUALITY = ? "
                    + "WHERE STATION_CODE = ? AND FACTOR_CODE = ? AND DATA_WEEK = ? AND DATA_YEAR = ? " 
                        + "WHEN NOT MATCHED THEN "
                    + "INSERT VALUES (?,?,?,TO_DATE(?,'yyyy-MM-dd'),TO_DATE(?,'yyyy-MM-dd'),?,?,?,?,?,?,?,?,?) ";
            pstmt = connection.prepareStatement(sql);
            int k = 0;
            for (Map<String, Object> map : weekListData) {
                String stationCode = map.get("STATION_CODE").toString();
                String projectCode = map.get("FACTOR_CODE").toString();
                String year = map.get("YEAR").toString();
                String week = map.get("WEEK").toString();
                String dataValueAvg = map.get("DATA_VALUE_AVG") == null ? "0" : map.get("DATA_VALUE_AVG").toString();
                String dataValueMax = map.get("DATA_VALUE_MAX") == null ? "0" : map.get("DATA_VALUE_MAX").toString();
                String dataValueMin = map.get("DATA_VALUE_MIN") == null ? "0" : map.get("DATA_VALUE_MIN").toString();
                String realNum = map.get("REAL_NUM") == null ? "0" : map.get("REAL_NUM").toString();
                // 计算周数的开始与结束日期
                Map<String, String> weekMap = DateUtil.weekStartEndTime(Integer.valueOf(year), Integer.valueOf(week));
                // 判断数据的有效性
                String dataFlag = "N"; //默认为有效
                if (Integer.valueOf(realNum) >= 5) { 
                    dataFlag = "N";
                } else {
                    dataFlag = "H";
                }
                // 计算因子的水质等级
                waterQuality = WaterQualityUtil.getWaterLevel(projectCode, dataValueAvg) + "";
                // 修约数据
                dataValueAvg = ScaleUtil.sciCalSix(Double.valueOf(dataValueAvg), 4);
                String uuid = UUIDGenerator.getUUID();
                // 判断是否存在
                pstmt.setString(1, stationCode);
                pstmt.setString(2, projectCode);
                pstmt.setString(3, week);
                pstmt.setString(4, year);
                pstmt.setString(5, dataValueAvg);
                pstmt.setString(6, dataValueMax);
                pstmt.setString(7, dataValueMin);
                pstmt.setString(8, "7"); // 一周7天数据
                pstmt.setString(9, realNum);
                pstmt.setString(10, dataFlag);
                pstmt.setString(11, waterQuality);
                pstmt.setString(12, stationCode);
                pstmt.setString(13, projectCode);
                pstmt.setString(14, week);
                pstmt.setString(15, year);
                pstmt.setString(16, uuid);
                pstmt.setString(17, stationCode);
                pstmt.setString(18, projectCode);
                pstmt.setString(19, weekMap.get("startTime"));
                pstmt.setString(20, weekMap.get("endTime"));
                pstmt.setString(21, week);
                pstmt.setString(22, year);
                pstmt.setString(23, dataValueAvg);
                pstmt.setString(24, dataValueMax);
                pstmt.setString(25, dataValueMin);
                pstmt.setString(26, realNum);
                pstmt.setString(27, "7");
                pstmt.setString(28, dataFlag);
                pstmt.setString(29, waterQuality);
                pstmt.addBatch();
                k++;
                if (k % 20 == 0) {
                    pstmt.executeBatch();
                }
            }
            pstmt.executeBatch();
            int updateNum = IDayDataRepository.updateDayMonthData(1);
            message = "周数据" + k + "条统计成功; 日数据" + updateNum + "条周标识更新成功！";
        } catch (Exception e) {
            e.printStackTrace();
        }
        return message;
    }

    @Override
    public String getMonthDataList(Connection connection, PreparedStatement pstmt, ResultEntity resultEntity) {
        System.out.println("****************" + DateUtil.getCurrentDate(DateUtil.DATE_FULL_STR) + " 开始统计月数据****************");
        String message = "";
        List<Map<String, Object>> monthList = ObjectMapperUtil.getMapList(resultEntity);
        List<String> monthTimeList = monthList.stream().map(l -> l.get("TIME").toString()).collect(Collectors.toList());
        if (monthTimeList.size() == 0) {
            message = "月数据暂无需要统计的数据！";
            return message;
        }
        List<Map<String, Object>> monthListData = IDayDataRepository.getCountMonthData(monthTimeList);
        try {
            // 编写Sql
            String sql = "MERGE INTO MONTH_DATA T1 " 
                        + "USING "
                        + "(SELECT COUNT(*) NUM FROM MONTH_DATA T WHERE T.STATION_CODE = ? AND T.FACTOR_CODE = ? " 
                        + "AND T.DATA_TIME = TO_DATE(?,'yyyy-MM')) T2 "
                    + "ON (T2.NUM > 0) " 
                        + "WHEN MATCHED THEN "
                    + "UPDATE SET DATA_VALUE_AVG = ?,DATA_VALUE_MAX = ?,DATA_VALUE_MIN = ?,STANDARD_NUM = ?,REAL_NUM = ?,"
                    + "DATA_FLAG = ?,WATER_QUALITY = ? "
                    + "WHERE STATION_CODE = ? AND FACTOR_CODE = ? AND DATA_TIME = TO_DATE(?,'yyyy-MM') " 
                        + "WHEN NOT MATCHED THEN "
                    + "INSERT VALUES (?,?,?,TO_DATE(?,'yyyy-MM'),?,?,?,?,?,?,?) ";
            pstmt = connection.prepareStatement(sql);
            int k = 0;
            for (Map<String, Object> map : monthListData) {
                String stationCode = map.get("STATION_CODE").toString();
                String projectCode = map.get("FACTOR_CODE").toString();
                String dateTime = map.get("TIME").toString();
                String dataValueAvg = map.get("DATA_VALUE_AVG") == null ? "0" : map.get("DATA_VALUE_AVG").toString();
                String dataValueMax = map.get("DATA_VALUE_MAX") == null ? "0" : map.get("DATA_VALUE_MAX").toString();
                String dataValueMin = map.get("DATA_VALUE_MIN") == null ? "0" : map.get("DATA_VALUE_MIN").toString();
                String realNum = map.get("REAL_NUM") == null ? "0" : map.get("REAL_NUM").toString();
                Date date = DateUtil.stringToDate(dateTime, DateUtil.DATE_SMALL_YM);
                int year = DateUtil.getYear(date);
                int month = DateUtil.getMonth(date);
                int month_day = DateUtil.getDaysByYearMonth(year, month); // 获取月份天数
                // 判断数据的有效性
                String dataFlag = "N"; //默认为有效
                if (Integer.valueOf(realNum) >= 27) { 
                    dataFlag = "N";
                } else {
                    dataFlag = "H";
                }
                // 计算因子的水质等级
                waterQuality = WaterQualityUtil.getWaterLevel(projectCode, dataValueAvg) + "";
                // 修约数据
                dataValueAvg = ScaleUtil.sciCalSix(Double.valueOf(dataValueAvg), 4);
                String uuid = UUIDGenerator.getUUID();
                // 判断是否存在
                pstmt.setString(1, stationCode);
                pstmt.setString(2, projectCode);
                pstmt.setString(3, dateTime);
                pstmt.setString(4, dataValueAvg);
                pstmt.setString(5, dataValueMax);
                pstmt.setString(6, dataValueMin);
                pstmt.setString(7, month_day + "");
                pstmt.setString(8, realNum);
                pstmt.setString(9, dataFlag);
                pstmt.setString(10, waterQuality);
                pstmt.setString(11, stationCode);
                pstmt.setString(12, projectCode);
                pstmt.setString(13, dateTime);
                pstmt.setString(14, uuid);
                pstmt.setString(15, stationCode);
                pstmt.setString(16, projectCode);
                pstmt.setString(17, dateTime);
                pstmt.setString(18, dataValueAvg);
                pstmt.setString(19, dataValueMax);
                pstmt.setString(20, dataValueMin);
                pstmt.setString(21, realNum);
                pstmt.setString(22, month_day + "");
                pstmt.setString(23, dataFlag);
                pstmt.setString(24, waterQuality);
                pstmt.addBatch();
                k++;
                if (k % 20 == 0) {
                    pstmt.executeBatch();
                }
            }
            pstmt.executeBatch();
            int updateNum = IDayDataRepository.updateDayMonthData(1);
            message = "月数据" + k + "条统计成功; 日数据" + updateNum + "条月标识更新成功！";
        } catch (Exception e) {
            e.printStackTrace();
        }
        return message;
    }

    @Override
    public String getQuarterDataList(Connection connection, PreparedStatement pstmt, ResultEntity resultEntity) {
        System.out.println("****************" + DateUtil.getCurrentDate(DateUtil.DATE_FULL_STR) + " 开始统计季度数据****************");
        String message = "";
        List<Map<String, Object>> quarterList = ObjectMapperUtil.getMapList(resultEntity);
        // 获取年、季度时间
        List<String> yearTimeList = new ArrayList<String>();
        List<String> quarTimeList = new ArrayList<String>();
        for (Map<String, Object> map : quarterList) {
            String year = map.get("YEAR").toString();
            String quarter = map.get("QUARTER").toString();
            yearTimeList.add(year);
            quarTimeList.add(quarter);
        }
        if (yearTimeList.size() == 0 || quarTimeList.size() == 0) {
            message = "季度数据暂无需要统计的数据！";
            return message;
        }
        List<Map<String, Object>> quarterListData = IDayDataRepository.getCountQuarterData(yearTimeList, quarTimeList);
        try {
            // 编写Sql
            String sql = "MERGE INTO QUARTER_DATA T1 " 
                        + "USING "
                        + "(SELECT COUNT(*) NUM FROM QUARTER_DATA T WHERE T.STATION_CODE = ? AND T.FACTOR_CODE = ? " 
                        + "AND T.YEAR = ? AND T.QUARTER = ?) T2 "
                    + "ON (T2.NUM > 0) " 
                        + "WHEN MATCHED THEN "
                    + "UPDATE SET DATA_VALUE_AVG = ?,DATA_VALUE_MAX = ?,DATA_VALUE_MIN = ?,STANDARD_NUM = ?,REAL_NUM = ?,"
                    + "DATA_FLAG = ?,WATER_QUALITY = ? "
                    + "WHERE STATION_CODE = ? AND FACTOR_CODE = ? AND YEAR = ? AND QUARTER = ? " 
                        + "WHEN NOT MATCHED THEN "
                    + "INSERT VALUES (?,?,?,?,?,?,?,?,?,?,?,?) ";
            pstmt = connection.prepareStatement(sql);
            int k = 0;
            for (Map<String, Object> map : quarterListData) {
                String stationCode = map.get("STATION_CODE").toString();
                String projectCode = map.get("FACTOR_CODE").toString();
                String year = map.get("YEAR").toString();
                String quarter = map.get("QUARTER").toString();
                String dataValueAvg = map.get("DATA_VALUE_AVG") == null ? "0" : map.get("DATA_VALUE_AVG").toString();
                String dataValueMax = map.get("DATA_VALUE_MAX") == null ? "0" : map.get("DATA_VALUE_MAX").toString();
                String dataValueMin = map.get("DATA_VALUE_MIN") == null ? "0" : map.get("DATA_VALUE_MIN").toString();
                String realNum = map.get("REAL_NUM") == null ? "0" : map.get("REAL_NUM").toString();
                Integer quarters = Integer.valueOf(quarter);
                // 判断季度所属的时间范围
                String quarterStartTime = "", quarterEndTime = "";
                if (quarters == 1) {
                    quarterStartTime = year + "-01-01";
                    quarterEndTime = year + "-03-31";
                } else if (quarters == 2) {
                    quarterStartTime = year + "-04-01";
                    quarterEndTime = year + "-06-30";
                } else if (quarters == 3) {
                    quarterStartTime = year + "-07-01";
                    quarterEndTime = year + "-09-30";
                } else if (quarters == 4) {
                    quarterStartTime = year + "-10-01";
                    quarterEndTime = year + "-12-31";
                }
                int differentDay = DateUtil.differentDays(DateUtil.stringToDate(quarterStartTime, DateUtil.DATE_SMALL_STR),
                        DateUtil.stringToDate(quarterEndTime, DateUtil.DATE_SMALL_STR));
                // 判断数据的有效性
                String dataFlag = "N"; //默认为有效
                if (Integer.valueOf(realNum) >= 81) { //一天内必须至少要有4个小时的数据，数据才具备有效性，否则有效性不足（一天6条小时数据，4小时一条）
                    dataFlag = "N";
                } else {
                    dataFlag = "H";
                }
                // 计算因子的水质等级
                waterQuality = WaterQualityUtil.getWaterLevel(projectCode, dataValueAvg) + "";
                // 修约数据
                dataValueAvg = ScaleUtil.sciCalSix(Double.valueOf(dataValueAvg), 4);
                String uuid = UUIDGenerator.getUUID();
                // 判断是否存在
                pstmt.setString(1, stationCode);
                pstmt.setString(2, projectCode);
                pstmt.setString(3, year);
                pstmt.setString(4, quarter);
                pstmt.setString(5, dataValueAvg);
                pstmt.setString(6, dataValueMax);
                pstmt.setString(7, dataValueMin);
                pstmt.setString(8, differentDay + "");
                pstmt.setString(9, realNum);
                pstmt.setString(10, dataFlag);
                pstmt.setString(11, waterQuality);
                pstmt.setString(12, stationCode);
                pstmt.setString(13, projectCode);
                pstmt.setString(14, year);
                pstmt.setString(15, quarter);
                pstmt.setString(16, uuid);
                pstmt.setString(17, stationCode);
                pstmt.setString(18, projectCode);
                pstmt.setString(19, year);
                pstmt.setString(20, quarter);
                pstmt.setString(21, dataValueAvg);
                pstmt.setString(22, dataValueMax);
                pstmt.setString(23, dataValueMin);
                pstmt.setString(24, realNum);
                pstmt.setString(25, differentDay + "");
                pstmt.setString(26, dataFlag);
                pstmt.setString(27, waterQuality);
                pstmt.addBatch();
                k++;
                if (k % 20 == 0) {
                    pstmt.executeBatch();
                }
            }
            pstmt.executeBatch();
            int updateNum = IDayDataRepository.updateDayQuarterData(1);
            message = "季度数据" + k + "条统计成功; 日数据" + updateNum + "条季度标识更新成功！";
        } catch (Exception e) {
            e.printStackTrace();
        }
        return message;
    }


    @Override
    public String getYearDataList(Connection connection, PreparedStatement pstmt, ResultEntity resultEntity) {
        System.out.println("****************" + DateUtil.getCurrentDate(DateUtil.DATE_FULL_STR) + " 开始统计年数据****************");
        String message = "";
        List<Map<String, Object>> yearList = ObjectMapperUtil.getMapList(resultEntity);
        List<String> yearTimeList = yearList.stream().map(l -> l.get("TIME").toString()).collect(Collectors.toList());
        if (yearTimeList.size() == 0) {
            message = "年数据暂无需要统计的数据！";
            return message;
        }
        List<Map<String, Object>> monthListData = IDayDataRepository.getCountYearData(yearTimeList);
        try {
            // 编写Sql
            String sql = "MERGE INTO YEAR_DATA T1 " 
                        + "USING "
                        + "(SELECT COUNT(*) NUM FROM YEAR_DATA T WHERE T.STATION_CODE = ? AND T.FACTOR_CODE = ? " 
                        + "AND T.YEAR = ?) T2 "
                    + "ON (T2.NUM > 0) " 
                        + "WHEN MATCHED THEN "
                    + "UPDATE SET DATA_VALUE_AVG = ?,DATA_VALUE_MAX = ?,DATA_VALUE_MIN = ?,STANDARD_NUM = ?,REAL_NUM = ?,"
                    + "DATA_FLAG = ?,WATER_QUALITY = ? "
                    + "WHERE STATION_CODE = ? AND FACTOR_CODE = ? AND YEAR = ? " 
                        + "WHEN NOT MATCHED THEN "
                    + "INSERT VALUES (?,?,?,?,?,?,?,?,?,?,?) ";
            pstmt = connection.prepareStatement(sql);
            int k = 0;
            for (Map<String, Object> map : monthListData) {
                String stationCode = map.get("STATION_CODE").toString();
                String projectCode = map.get("FACTOR_CODE").toString();
                String year = map.get("YEAR").toString();
                String dataValueAvg = map.get("DATA_VALUE_AVG") == null ? "0" : map.get("DATA_VALUE_AVG").toString();
                String dataValueMax = map.get("DATA_VALUE_MAX") == null ? "0" : map.get("DATA_VALUE_MAX").toString();
                String dataValueMin = map.get("DATA_VALUE_MIN") == null ? "0" : map.get("DATA_VALUE_MIN").toString();
                String realNum = map.get("REAL_NUM") == null ? "0" : map.get("REAL_NUM").toString();
                // 计算开始与结束时间相差天数
                String startTime = "" + year + "-01-01";
                String endTime = "" + year + "-12-31";
                int differentDay = DateUtil.differentDays(DateUtil.stringToDate(startTime, DateUtil.DATE_SMALL_STR),
                        DateUtil.stringToDate(endTime, DateUtil.DATE_SMALL_STR));
                // 判断数据的有效性
                String dataFlag = "N"; //默认为有效
                if (Integer.valueOf(realNum) >= 324) { //一天内必须至少要有4个小时的数据，数据才具备有效性，否则有效性不足（一天6条小时数据，4小时一条）
                    dataFlag = "N";
                } else {
                    dataFlag = "H";
                }
                // 计算因子的水质等级
                waterQuality = WaterQualityUtil.getWaterLevel(projectCode, dataValueAvg) + "";
                // 修约数据
                dataValueAvg = ScaleUtil.sciCalSix(Double.valueOf(dataValueAvg), 4);
                String uuid = UUIDGenerator.getUUID();
                // 判断是否存在
                pstmt.setString(1, stationCode);
                pstmt.setString(2, projectCode);
                pstmt.setString(3, year);
                pstmt.setString(4, dataValueAvg);
                pstmt.setString(5, dataValueMax);
                pstmt.setString(6, dataValueMin);
                pstmt.setString(7, differentDay + "");
                pstmt.setString(8, realNum);
                pstmt.setString(9, dataFlag);
                pstmt.setString(10, waterQuality);
                pstmt.setString(11, stationCode);
                pstmt.setString(12, projectCode);
                pstmt.setString(13, year);
                pstmt.setString(14, uuid);
                pstmt.setString(15, stationCode);
                pstmt.setString(16, projectCode);
                pstmt.setString(17, year);
                pstmt.setString(18, dataValueAvg);
                pstmt.setString(19, dataValueMax);
                pstmt.setString(20, dataValueMin);
                pstmt.setString(21, realNum);
                pstmt.setString(22, differentDay + "");
                pstmt.setString(23, dataFlag);
                pstmt.setString(24, waterQuality);
                pstmt.addBatch();
                k++;
                if (k % 20 == 0) {
                    pstmt.executeBatch();
                }
            }
            pstmt.executeBatch();
            int updateNum = IDayDataRepository.updateDayYearData(1);
            message = "年数据" + k + "条统计成功; 日数据" + updateNum + "条年标识更新成功！";
        } catch (Exception e) {
            e.printStackTrace();
        }
        return message;
    }

}
